﻿using System;
using System.Text;
using Sanelib.Common.SystemSettings;

namespace Sanelib.CodeMaker.Generators
{
    public class DatabaseScriptGenerator
    {
        public static void Generate(DatabaseTableSchema tbSchema)
        {
            var sb = new StringBuilder();
            CreatedDefaultSettings(sb);
            CreateTable(tbSchema, sb);
            CreateLogTable(tbSchema, sb);
            CreateView(tbSchema, sb);
            CreateInsertRecordProcedure(tbSchema, sb);
            CreateUpdateRecordProcedure(tbSchema, sb);
            CreateDeleteRecordProcedure(tbSchema, sb);

            System.IO.File.WriteAllText(Globals.DatabaseScriptFolder + "\\Create" + tbSchema.TableName + ".sql", sb.ToString());

        }

        private static void CreatedDefaultSettings(StringBuilder sb)
        {
            sb.AppendLine("SET ANSI_NULLS ON");
            sb.AppendLine("GO");
            sb.AppendLine("");
            sb.AppendLine("SET QUOTED_IDENTIFIER ON");
            sb.AppendLine("GO");
            sb.AppendLine("");
        }

        private static void CreateTable(DatabaseTableSchema tbSchema, StringBuilder sb)
        {
            sb.AppendLine("");
            sb.AppendLine("IF OBJECT_ID('dbo.[" + tbSchema.TableName + "]', N'U') IS NOT NULL");
            sb.AppendLine("DROP TABLE [dbo].[" + tbSchema.TableName + "]");
            sb.AppendLine("GO");
            sb.AppendLine("");
            sb.AppendLine("/****** Object:  Table [dbo].[" + tbSchema.TableName + "]    Script Date: " + DateTime.Now + " ******/");
            sb.AppendLine("CREATE TABLE [dbo].[" + tbSchema.TableName + "](");
            sb.AppendLine("\t[Id] [uniqueidentifier] NOT NULL,");

            if (tbSchema.IsAudited) sb.AppendLine("\t[Name] [nvarchar](256) NOT NULL,");
            
            foreach (var item in tbSchema.Columns)
            {
                sb.AppendLine("\t" + item.GetColumnDeclaration());
            }

            if (tbSchema.IsAudited)
            {
                sb.AppendLine("\t[Description] [nvarchar](2048) NULL,");
                sb.AppendLine("\t[ImageData] [varchar](max) NULL,");
                sb.AppendLine("\t[RevisionNumber] [int] NULL,");
                sb.AppendLine("\t[CreatedBy] [uniqueidentifier] NULL,");
                sb.AppendLine("\t[CreatedOn] [datetime] NULL,");
                sb.AppendLine("\t[ModifiedBy] [uniqueidentifier] NULL,");
                sb.AppendLine("\t[ModifiedOn] [datetime] NULL,");
            }

            sb.AppendLine("CONSTRAINT [PK_" + tbSchema.TableName + "] PRIMARY KEY CLUSTERED ");
            sb.AppendLine("\t([Id] ASC)");
            sb.AppendLine("\tWITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]");
            sb.AppendLine("\t) ON [PRIMARY]");
            sb.AppendLine("GO");
            sb.AppendLine("");
        }

        private static void CreateLogTable(DatabaseTableSchema tbSchema, StringBuilder sb)
        {
            if (!tbSchema.IsAudited) return;

            sb.AppendLine("");
            sb.AppendLine("IF OBJECT_ID('dbo.[" + tbSchema.TableName + "_Log]', N'U') IS NOT NULL");
            sb.AppendLine("DROP TABLE [dbo].[" + tbSchema.TableName + "_Log]");
            sb.AppendLine("GO");
            sb.AppendLine("");
            sb.AppendLine("/****** Object:  Table [dbo].[" + tbSchema.TableName + "_Log]    Script Date: " + DateTime.Now + " ******/");
            sb.AppendLine(""); sb.AppendLine("CREATE TABLE [dbo].[" + tbSchema.TableName + "_Log](");
            sb.AppendLine("\t[Sr] [bigint] IDENTITY(1,1) NOT NULL,");
            sb.AppendLine("\t[Id] [uniqueidentifier] NOT NULL,");

            sb.AppendLine("\t[Name] [nvarchar](256) NOT NULL,");

            foreach (var item in tbSchema.Columns)
            {
                sb.AppendLine("\t" + item.GetColumnDeclaration());
            }

            sb.AppendLine("\t[Description] [nvarchar](2048) NULL,");
            sb.AppendLine("\t[ImageData] [varchar](max) NULL,");
            
            sb.AppendLine("\t[RevisionNumber] [int] NULL,");
            sb.AppendLine("\t[CreatedBy] [uniqueidentifier] NULL,");
            sb.AppendLine("\t[CreatedOn] [datetime] NULL,");
            sb.AppendLine("\t[ModifiedBy] [uniqueidentifier] NULL,");
            sb.AppendLine("\t[ModifiedOn] [datetime] NULL,");

            sb.AppendLine("CONSTRAINT [PK_" + tbSchema.TableName + "_Log] PRIMARY KEY CLUSTERED ");
            sb.AppendLine("\t([Sr] ASC)");
            sb.AppendLine("\tWITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]");
            sb.AppendLine("\t) ON [PRIMARY]");
            sb.AppendLine("GO");
            sb.AppendLine("SET ANSI_PADDING OFF");
            sb.AppendLine("GO");
            sb.AppendLine("");
        }

        private static string GetSelectQuery(DatabaseTableSchema tbSchema, bool forView = false)
        {
            var sb = new StringBuilder();

            var alias = tbSchema.GetTableAlias();

            sb.AppendLine("\tSELECT");
            sb.AppendLine("\t" + alias + ".[Id]");

            if(tbSchema.IsAudited) sb.AppendLine("\t," + alias + ".[Name]");

            foreach (var item in tbSchema.Columns)
            {
                if(item.DataType == DataType.Enumeration) sb.AppendLine("\t," + alias + ".[" + item.PropertyName + "Value]");
                else sb.AppendLine("\t," + alias + ".[" + item.PropertyName + "]");
            }

            if (tbSchema.IsAudited)
            {
                sb.AppendLine("\t," + alias + ".[Description]");
                sb.AppendLine("\t," + alias + ".[ImageData]");
                sb.AppendLine("\t," + alias + ".[RevisionNumber]");
                sb.AppendLine("\t," + alias + ".[CreatedBy]");
                if(forView) sb.AppendLine("\t,CM.[Name] AS CreatedByName");
                sb.AppendLine("\t," + alias + ".[CreatedOn]");
                sb.AppendLine("\t," + alias + ".[ModifiedBy]");
                if (forView) sb.AppendLine("\t,MM.[Name] AS ModifiedByName");
                sb.AppendLine("\t," + alias + ".[ModifiedOn]");
            }

            sb.AppendLine("\tFrom " + tbSchema.TableName + " " + alias);

            if (!forView) return sb.ToString();

            if (!tbSchema.IsAudited) return sb.ToString();
            sb.AppendLine("\tLEFT JOIN Member CM ON " + alias + ".[CreatedBy] = CM.[Id]");
            sb.AppendLine("\tLEFT JOIN Member MM ON " + alias + ".[ModifiedBy] = MM.[Id]");


            return sb.ToString();
        }

        private static void CreateView(DatabaseTableSchema tbSchema, StringBuilder sb)
        {
            sb.AppendLine("");
            sb.AppendLine("IF OBJECT_ID('dbo.[" + tbSchema.TableName + "View]', 'V') IS NOT NULL");
            sb.AppendLine("DROP VIEW [dbo].[" + tbSchema.TableName + "View]");
            sb.AppendLine("GO");
            sb.AppendLine("");
            sb.AppendLine("/****** Object:  View [dbo].[" + tbSchema.TableName + "View]    Script Date: " + DateTime.Now + " ******/");
            sb.AppendLine("CREATE VIEW [dbo].[" + tbSchema.TableName + "View]" + Environment.NewLine + "AS");

            sb.AppendLine(GetSelectQuery(tbSchema, true));

            sb.AppendLine("GO" + Environment.NewLine + "SET ANSI_PADDING OFF " + Environment.NewLine + "GO");
            sb.AppendLine("");
        }

        private static string GetProcedureParameters(DatabaseTableSchema tbSchema)
        {
            var sb = new StringBuilder();

            sb.AppendLine("\t@Id  [uniqueidentifier]");

            if (tbSchema.IsAudited) sb.AppendLine("\t,@Name [nvarchar](256)");

            foreach (var item in tbSchema.Columns)
            {
                sb.AppendLine("\t" + item.GetColumnParameter());
            }

            if (!tbSchema.IsAudited) return sb.ToString();
            
            sb.AppendLine("\t,@Description [nvarchar](2048)");
            sb.AppendLine("\t,@ImageData [varchar](max)");
            sb.AppendLine("\t,@UserID  [uniqueidentifier]");

            return sb.ToString();
        }

        private static void CreateInsertRecordProcedure(DatabaseTableSchema tbSchema, StringBuilder sb)
        {
            sb.AppendLine("");
            sb.AppendLine("IF OBJECT_ID('dbo.[usp_" + tbSchema.TableName + "Insert]', N'P') IS NOT NULL");
            sb.AppendLine("DROP PROCEDURE[dbo].[usp_" + tbSchema.TableName + "Insert]");
            sb.AppendLine("GO");
            sb.AppendLine("");
            sb.AppendLine("/****** Object:  StoredProcedure [dbo].[usp_" + tbSchema.TableName + "Insert]    Script Date: " + DateTime.Now + " ******/");
            sb.AppendLine("CREATE PROC [dbo].[usp_" + tbSchema.TableName + "Insert]");

            sb.AppendLine(GetProcedureParameters(tbSchema));

            sb.AppendLine("AS");

            sb.AppendLine("\tSET NOCOUNT ON ");
            sb.AppendLine("\tSET XACT_ABORT ON");
            sb.AppendLine("");
            sb.AppendLine("\tBEGIN TRAN");
            sb.AppendLine("");
            sb.Append("\tINSERT INTO [dbo].[" + tbSchema.TableName + "] ([Id]");

            if (tbSchema.IsAudited) sb.Append(", [Name]");

            foreach (var item in tbSchema.Columns)
            {
                if (item.DataType == DataType.Enumeration) sb.Append(", [" + item.PropertyName + "Value]");
                else sb.Append(", [" + item.PropertyName + "]");
            }

            sb.Append(tbSchema.IsAudited ? ", [Description], [ImageData], [RevisionNumber], [CreatedBy], [CreatedOn], [ModifiedBy], [ModifiedOn])" : ")");

            sb.AppendLine("");

            sb.Append("\tSELECT @Id");

            if (tbSchema.IsAudited) sb.Append(", @Name");

            foreach (var item in tbSchema.Columns)
            {
                if (item.DataType == DataType.Enumeration) sb.Append(", @" + item.PropertyName + "Value");
                else sb.Append(", @" + item.PropertyName);
            }

            if (tbSchema.IsAudited)
            {
                sb.Append(", @Description, @ImageData, 0, @UserId, GETDATE(), NULL, NULL");
            }

            sb.AppendLine("");

            if (tbSchema.IsAudited)
            {
                sb.AppendLine("");

                sb.Append("\tINSERT INTO [dbo].[" + tbSchema.TableName + "_log] ([Id]");

                if (tbSchema.IsAudited) sb.Append(", [Name]");

                foreach (var item in tbSchema.Columns)
                {
                    if (item.DataType == DataType.Enumeration) sb.Append(", [" + item.PropertyName + "Value]");
                    else sb.Append(", [" + item.PropertyName + "]");
                }

                if (tbSchema.IsAudited)
                {
                    sb.Append(", [Description], [ImageData], [RevisionNumber], [CreatedBy], [CreatedOn], [ModifiedBy], [ModifiedOn])");
                }

                sb.AppendLine("");

                sb.AppendLine(GetSelectQuery(tbSchema));
                sb.AppendLine("\tWHERE " + tbSchema.GetTableAlias() + ".[Id] = @Id");

                sb.AppendLine("");
            }

            sb.AppendLine("");
            sb.AppendLine("\tCOMMIT;");
            sb.AppendLine("");

            sb.Append(GetSelectQuery(tbSchema));

            sb.AppendLine("\tWHERE " + tbSchema.GetTableAlias() + ".[Id] = @Id"); 

            sb.AppendLine("");
            sb.AppendLine("GO");
            sb.AppendLine("");
        }

        private static void CreateUpdateRecordProcedure(DatabaseTableSchema tbSchema, StringBuilder sb)
        {
            sb.AppendLine("");
            sb.AppendLine("IF OBJECT_ID('dbo.[usp_" + tbSchema.TableName + "Update]', N'P') IS NOT NULL");
            sb.AppendLine("DROP PROCEDURE[dbo].[usp_" + tbSchema.TableName + "Update]");
            sb.AppendLine("GO");
            sb.AppendLine("");
            sb.AppendLine("/****** Object:  StoredProcedure [dbo].[usp_" + tbSchema.TableName + "Update]    Script Date: " + DateTime.Now + " ******/");
            sb.AppendLine("CREATE PROC [dbo].[usp_" + tbSchema.TableName + "Update]");

            sb.AppendLine(GetProcedureParameters(tbSchema));

            sb.AppendLine("AS");

            sb.AppendLine("\tSET NOCOUNT ON ");
            sb.AppendLine("\tSET XACT_ABORT ON");
            sb.AppendLine("");
            sb.AppendLine("\tBEGIN TRAN");
            sb.AppendLine("");
            sb.AppendLine("\tUPDATE [dbo].[" + tbSchema.TableName + "]");

            sb.Append("\tSET [Id] = @Id");

            if (tbSchema.IsAudited) sb.Append(", [Name] = @Name");

            foreach (var item in tbSchema.Columns)
            {
                if (item.DataType == DataType.Enumeration) sb.Append(", [" + item.PropertyName + "Value] = @" + item.PropertyName + "Value");
                else sb.Append(", [" + item.PropertyName + "] = @" + item.PropertyName);
            }

            if (tbSchema.IsAudited)
            {
                sb.Append(", [Description] = @Description, [ImageData] = @ImageData, [RevisionNumber] = [RevisionNumber] + 1, [ModifiedBy] = @UserId, [ModifiedOn] = GETDATE()");
            }

            sb.AppendLine("\tWHERE [Id] = @Id");

            sb.AppendLine("");

            if (tbSchema.IsAudited)
            {
                sb.AppendLine("");

                sb.Append("\tINSERT INTO [dbo].[" + tbSchema.TableName + "_log] ([Id]");

                if (tbSchema.IsAudited) sb.Append(", [Name]");

                foreach (var item in tbSchema.Columns)
                {
                    if (item.DataType == DataType.Enumeration) sb.Append(", [" + item.PropertyName + "Value]");
                    else sb.Append(", [" + item.PropertyName + "]");
                }

                if (tbSchema.IsAudited)
                {
                    sb.Append(", [Description], [ImageData], [RevisionNumber], [CreatedBy], [CreatedOn], [ModifiedBy], [ModifiedOn])");
                }

                sb.AppendLine("");

                sb.AppendLine(GetSelectQuery(tbSchema));

                sb.AppendLine("\tWHERE " + tbSchema.GetTableAlias() + ".[Id] = @Id"); 

                sb.AppendLine("");
            }

            sb.AppendLine("");
            sb.AppendLine("\tCOMMIT;");
            sb.AppendLine("");

            sb.Append(GetSelectQuery(tbSchema));

            sb.AppendLine("\tWHERE " + tbSchema.GetTableAlias() + ".[Id] = @Id"); 

            sb.AppendLine("");
            sb.AppendLine("GO");
            sb.AppendLine("");
        }

        private static void CreateDeleteRecordProcedure(DatabaseTableSchema tbSchema, StringBuilder sb)
        {
            sb.AppendLine("");
            sb.AppendLine("IF OBJECT_ID('dbo.[usp_" + tbSchema.TableName + "Delete]', N'P') IS NOT NULL");
            sb.AppendLine("DROP PROCEDURE[dbo].[usp_" + tbSchema.TableName + "Delete]");
            sb.AppendLine("GO");
            sb.AppendLine("");
            sb.AppendLine("/****** Object:  StoredProcedure [dbo].[usp_" + tbSchema.TableName + "Delete]    Script Date: " + DateTime.Now + " ******/");
            sb.AppendLine("CREATE PROC [dbo].[usp_" + tbSchema.TableName + "Delete]");
            sb.AppendLine("\t@Id uniqueidentifier");
            sb.AppendLine("AS");
            sb.AppendLine("SET NOCOUNT ON");
            sb.AppendLine("SET XACT_ABORT ON");
            sb.AppendLine("");
            sb.AppendLine("BEGIN TRAN");
            sb.AppendLine("\tDELETE FROM [dbo].[" + tbSchema.TableName + "] WHERE  [Id] = @Id");
            sb.AppendLine("COMMIT;");
            sb.AppendLine("GO");
            sb.AppendLine("");
        }
    }
}
